/*
 * Powered By [rapid-framework]
 * Web Site: http://www.rapid-framework.org.cn
 * Google Code: http://code.google.com/p/rapid-framework/
 * Since 2008 - 2016
 */

package com.jzwl.site.manager.rankv2.dao;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.stereotype.Repository;

import com.jzwl.common.constant.Constants;
import com.jzwl.common.id.Sequence;

import com.jzwl.system.base.dao.BaseDAO;
import com.jzwl.common.id.Sequence;
import com.jzwl.common.page.PageObject;
import com.jzwl.site.manager.rankv2.pojo.*;
import com.jzwl.site.manager.rankv2.dao.*;
import com.jzwl.site.manager.rankv2.service.*;



@Repository("siteRankingV2Dao")
public class SiteRankingV2Dao {
	
	@Autowired
	private BaseDAO baseDAO;//dao基类，操作数据库
	
	@Autowired
	private Constants constants;
	
	public boolean addSiteRankingV2(Map<String, Object> map) {
		
		//自动注入时间戳为ID 酌情修改数据库类型为bigint  int会越界
		map.put("rankid",  Sequence.nextId());
		
		String sql = "insert into " + constants.getCurrentDB() + "site_ranking_v2 " 
				 + " (rankid,type,rangking,scholName,teamName,produce,award) " 
				 + " values "
				 + " (:rankid,:type,:rangking,:scholName,:teamName,:produce,:award)";
		
		return baseDAO.executeNamedCommand(sql, map);
	}
	
	
	public String getColumns() {
		return ""
				+" rankid as rankid,"
				+" type as type,"
				+" rangking as rangking,"
				+" scholName as scholName,"
				+" teamName as teamName,"
				+" produce as produce,"
				+" award as award"
				;
	}
	
	public String getColumnslike() { //case when then 专用
		return ""
				+" rankid as rankid,"
				+" CASE type WHEN 1 THEN '校区排名' WHEN 2 THEN '大区排名' WHEN 3 THEN '全国排名' END type,"
				+" rangking as rangking,"
				+" scholName as scholName,"
				+" teamName as teamName,"
				+" produce as produce,"
				+" award as award"
				;
	}

	public PageObject querySiteRankingV2List(Map<String,Object> map) {
		// [column]为字符串拼接, {column}为使用占位符. 如username='[username]',偷懒时可以使用字符串拼接 
		// [column] 为PageRequest的属性
		
		String sql="select " + getColumnslike() + " from " + constants.getCurrentDB() + "site_ranking_v2 t where 1=1 ";
		
		  	
		  		if(null !=map.get("type") && StringUtils.isNotEmpty(map.get("type").toString())){
				  		sql=sql+ " and t.type  = " + map.get("type") +"";
			  	}
		  	
		  		if(null !=map.get("rangking") && StringUtils.isNotEmpty(map.get("rangking").toString())){
				  		sql=sql+ " and t.rangking  = " + map.get("rangking") +"";
			  	}
		  	
		  		if(null !=map.get("scholName") && StringUtils.isNotEmpty(map.get("scholName").toString())){
				  		sql=sql+ " and t.scholName  like '%" + map.get("scholName") +"%' ";	
			  	}
		  	
		  		if(null !=map.get("teamName") && StringUtils.isNotEmpty(map.get("teamName").toString())){
				  		sql=sql+ " and t.teamName  = " + map.get("teamName") +"";
			  	}
		  	
		  		if(null !=map.get("produce") && StringUtils.isNotEmpty(map.get("produce").toString())){
				  		sql=sql+ " and t.produce  = " + map.get("produce") +"";
			  	}
		  	
		  		if(null !=map.get("award") && StringUtils.isNotEmpty(map.get("award").toString())){
				  		sql=sql+ " and t.award  = " + map.get("award") +"";
			  	}
					
					
		sql=sql+ " order by  type asc";
		
		PageObject po = baseDAO.queryForMPageList(sql, new Object[]{},map);
		
		return po;
	}

	public boolean updateSiteRankingV2(Map<String, Object> map) {
		
		String sql ="update " + constants.getCurrentDB() + "site_ranking_v2 set ";
				  	if(null !=map.get("type") && StringUtils.isNotEmpty(map.get("type").toString())){
					  	sql=sql+ "			type=:type ,";
				  	}
				  	if(null !=map.get("rangking") && StringUtils.isNotEmpty(map.get("rangking").toString())){
					  	sql=sql+ "			rangking=:rangking ,";
				  	}
				  	if(null !=map.get("scholName") && StringUtils.isNotEmpty(map.get("scholName").toString())){
					  	sql=sql+ "			scholName=:scholName ,";
				  	}
				  	if(null !=map.get("teamName") && StringUtils.isNotEmpty(map.get("teamName").toString())){
					  	sql=sql+ "			teamName=:teamName ,";
				  	}
				  	if(null !=map.get("produce") && StringUtils.isNotEmpty(map.get("produce").toString())){
					  	sql=sql+ "			produce=:produce ,";
				  	}
				  	if(null !=map.get("award") && StringUtils.isNotEmpty(map.get("award").toString())){
					  	sql=sql+ "			award=:award ,";
				  	}
				sql = sql.substring(0, sql.length()-1);
				sql = sql + " where rankid=:rankid";
		return  baseDAO.executeNamedCommand(sql, map);
	}

	public boolean deleteSiteRankingV2(Map<String, Object> map) {
		
		String sql = "delete from " + constants.getCurrentDB() + "site_ranking_v2 where rankid in ("+ map.get("rankid")+ " ) ";

		return baseDAO.executeNamedCommand(sql, map);
	}

	public boolean delSiteRankingV2(Map<String, Object> map) {
		
		String sql ="update " + constants.getCurrentDB() + "site_ranking_v2 set "
				+ " isDelete =:"+map.get("isDelete")+" "
				+ " where rankid=:rankid";
		
		return  baseDAO.executeNamedCommand(sql, map);
	}
	
	
	@SuppressWarnings("unchecked")
	public Map<String,Object> getByRankid(Map<String, Object> map) {
		
		String sql = "select " + getColumns() + " from " + constants.getCurrentDB() + "site_ranking_v2 where rankid = "+ map.get("rankid") + "";
		
		return baseDAO.queryForMap(sql);
	}
	
	public List<Map<String,Object>> querySelectValue(Map<String,Object> map) {
		
		String sql = " SELECT dd.id,dd.dic_name "
				+ "    from dic_type  dt "
				+ "    LEFT JOIN  dic_data dd on dt.dic_id = dd.dic_id "
				+ "    where dt.dic_code = '"+map.get("code")+"'  and dd.isDelete = 0 ";
	 return	baseDAO.queryForList(sql);
	}
	

}
